-- 创建更新时间触发器函数
create or replace function trigger_set_timestamp()
returns trigger as $$
begin
  new.updated_at = now();
  return new;
end;
$$ language plpgsql;

-- 创建教师表
create table if not exists teachers (
  id uuid default uuid_generate_v4() primary key,
  name varchar(100) not null,
  phone varchar(20),
  email varchar(100),
  subject varchar(50),
  status varchar(20) default 'active',
  created_at timestamp with time zone default now(),
  updated_at timestamp with time zone default now()
);

-- 创建教师与班级关联表
create table if not exists teacher_classes (
  id uuid default uuid_generate_v4() primary key,
  teacher_id uuid references teachers(id) on delete cascade,
  class_id uuid references classes(id) on delete cascade,
  created_at timestamp with time zone default now()
);

-- 创建或更新教师表的更新时间触发器
drop trigger if exists set_timestamp on teachers;
create trigger set_timestamp
before update on teachers
for each row
execute procedure trigger_set_timestamp();
